package com.eascs.admin.o2o.loan.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.eascs.admin.common.service.BaseSevice;
import com.eascs.admin.o2o.internal.util.StatusContexts;
import com.eascs.admin.o2o.loan.dao.O2oLoanUserDao;
import com.eascs.admin.o2o.loan.entity.O2oLoanUserEntity;
import com.eascs.admin.o2o.loan.form.LoanUserForm;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.common.util.StringUtil;
import com.eascs.datalist.entity.O2oDataListEntity;

@Service
public class O2oLoanUserService extends BaseSevice {

	@Autowired
	private O2oLoanUserDao loanDao;

	public PageResult<Object[]> findByPage(int pageSize, int pageIndex, LoanUserForm loanUserForm) {
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<Object[]>(Object[].class, pageSize, pageIndex);
		StringBuffer sql = new StringBuffer();
		sql.append(
				"select olu.id,olu.user_full_name,olu.user_phone,pd.item_name,olu.document_no from o2o_loan_user olu JOIN pub_dditem pd on olu.document_type = pd.item_code where 1=1 ");
		if (StringUtil.isNotBlank(loanUserForm.getUserFullName())) {
			sql.append(" and olu.user_full_name like ? ");
			pageQuery.addLikeParam(loanUserForm.getUserFullName());
		}
		if (StringUtil.isNotBlank(loanUserForm.getDocumentType())) {
			sql.append(" and olu.document_type = ?");
			pageQuery.addParam(loanUserForm.getDocumentType());
		}
		if (StringUtil.isNotBlank(loanUserForm.getDocumentNo())) {
			sql.append(" and olu.document_no like ? ");
			pageQuery.addLikeParam(loanUserForm.getDocumentNo());
		}
		if (StringUtil.isNotBlank(loanUserForm.getUserPhone())) {
			sql.append(" and olu.user_phone like ?");
			pageQuery.addLikeParam(loanUserForm.getUserPhone());
		}
		pageQuery.setQuerySql(sql.toString());
		PageResult<Object[]> og = loanDao.findDataWithPage(pageQuery);
		return og;
	}

	public O2oLoanUserEntity findById(String id, String loanapplyNo) {
		return loanDao.queryLoanpplyNo(loanapplyNo, id);
	}

	/**
	 * @Description:
	 *               <p>
	 *               脱敏
	 *               </p>
	 * @author LMX
	 * @date 2018年3月6日 下午5:39:50
	 */
	public O2oLoanUserEntity findById_sensitive(String id, String loanapplyNo) {
		return loanDao.queryLoanpplyNo_sensitive(loanapplyNo, id);
	}

	public List<O2oLoanUserEntity> queryUsersByLoanapplyNo(String loanapplyNo) {
		return loanDao.queryUsersByLoanapplyNo(loanapplyNo);
	}

	/**
	 * @Title: findByLoanapplyNo
	 * @Description: 根据单款单号查询主贷款人信息
	 * @author 张庆奇
	 * @param loanapplyNo
	 * @return
	 * @return O2oLoanUserEntity
	 * @throws @date
	 *             2017年10月12日 上午9:41:49
	 */
	public O2oLoanUserEntity findByLoanapplyNo(String loanapplyNo) {
		return loanDao.findByLoanapplyNo(loanapplyNo);
	}

	public O2oLoanUserEntity queryById(String id) {
		return loanDao.getById(O2oLoanUserEntity.class, id);
	}

	public O2oLoanUserEntity findSponsorById(String id, String loanapplyNo) {
		return loanDao.querySponsorById(loanapplyNo, id);
	}

	/**
	 * @Description:
	 *               <p>
	 *               脱敏
	 *               </p>
	 * @author LMX
	 * @date 2018年3月6日 下午4:57:51
	 */
	public O2oLoanUserEntity findSponsorById_sensitive(String id, String loanapplyNo) {
		return loanDao.querySponsorById_sensitive(loanapplyNo, id);
	}

	public O2oLoanUserEntity findSpouseId(String loanId, String loanapplyNo) {
		return loanDao.findSpouseId(loanId, loanapplyNo);
	}

	/**
	 * @Description:
	 *               <p>
	 *               脱敏
	 *               </p>
	 * @author LMX
	 * @date 2018年3月6日 下午5:10:45
	 */
	public O2oLoanUserEntity findSpouseId_sensitive(String loanId, String loanapplyNo) {
		return loanDao.findSpouseId_sensitive(loanId, loanapplyNo);
	}

	@Transactional(transactionManager = "o2oTransactionManager")
	public int updateLoanUserEntity(O2oLoanUserEntity loanEntity) {
		return loanDao.updateById(loanEntity);
	}

	public int updateEntityById(O2oLoanUserEntity loanEntity) {
		StringBuffer sql = new StringBuffer();
		sql.append("update o2o_loan_user set ");
		if (StringUtil.isNotBlank(loanEntity.getDocumentNo())) {
			sql.append("document_no= ");
			sql.append(loanEntity.getDocumentNo());
		}
		if (StringUtil.isNotBlank(loanEntity.getUserNamePinyin())) {
			sql.append(" user_name_pinyin=");
			sql.append(loanEntity.getUserNamePinyin());
		}
		if (StringUtil.isNotBlank(loanEntity.getUserName())) {
			sql.append(" user_name= ");
			sql.append(loanEntity.getUserName());
		}
		if (StringUtil.isNotBlank(loanEntity.getUserFullName())) {
			sql.append(" user_full_name= ");
			sql.append(loanEntity.getUserFullName());
		}
		if (StringUtil.isNotBlank(loanEntity.getUserPhone())) {
			sql.append(" user_phone= ");
			sql.append(loanEntity.getUserPhone());
		}
		if (StringUtil.isNotBlank(loanEntity.getUserPhone())) {
			sql.append(" user_surname= ");
			sql.append(loanEntity.getUserPhone());
		}
		if (StringUtil.isNotBlank(loanEntity.getDocumentType())) {
			sql.append(" document_type= ");
			sql.append(loanEntity.getDocumentType());
		}
		if (StringUtil.isNotBlank(loanEntity.getDocumentType())) {
			sql.append(" where id=? ");
			sql.append(loanEntity.getDocumentType());
		}
		return loanDao.execute(sql.toString());
	}

	public int deleteEntityById(String id) {
		return loanDao.deleteById(O2oLoanUserEntity.class, id);
	}

	@Transactional(transactionManager = "o2oTransactionManager")
	public int saveEntityById(O2oLoanUserEntity loanEntity) {
		return loanDao.insert(loanEntity);
	}

	/*
	 * 删除担保人以及附件
	 */
	@Transactional(transactionManager = "o2oTransactionManager")
	public int deleteSponsor(O2oLoanUserEntity sponsor, List<O2oDataListEntity> sponsorFileList) {
		loanDao.deleteByIdWithEntity(sponsor);
		for (int i = 0; i < sponsorFileList.size(); i++) {
			sponsorFileList.get(i).setIsDelete(StatusContexts.O2O_YES);
			loanDao.updateById(sponsorFileList.get(i));
		}
		return 1;
	}

	/*
	 * 修改状态，然wrokeflow去跑批拿征信
	 */
	@Transactional(transactionManager = "o2oTransactionManager")
	public int startSponsorCredit(O2oLoanUserEntity sponsor) {
		return loanDao.updateById(sponsor);
	}

	/**
	 * 校验当前贷款身份证和姓名 ，与上笔贷款身份证和姓名是否匹配
	 * 
	 * @param loanId
	 * @param userFullName
	 * @param documentNo
	 * @return
	 */
	public int isNotExist(String loanId, String userFullName, String documentNo) {
		return loanDao.isNotExist(loanId, userFullName, documentNo);
	}

	public List<O2oLoanUserEntity> findSponsor(String loanapplyNo) {
		return loanDao.findSponsor(loanapplyNo);
	}

	public PageResult<Object[]> findSponsor(LoanUserForm loanUserForm) {

		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<Object[]>(Object[].class, Integer.MAX_VALUE, 0);
		StringBuffer sql = new StringBuffer();
		sql.append("select  ");
		sql.append(
				"(select datalist_no from o2o_datalist l where  l.loanapply_no = u.zx_no and l.datalist_code='ZXBGDATA' and l.is_delete = 'N' limit 1) as datalist_no,");
		sql.append(
				"(select datalist_no from o2o_datalist l where  l.loanapply_no = u.zx_no and l.datalist_code='Warrant_ZXBGDATA' and l.is_delete = 'N' limit 1) as datalist_no1,");
		sql.append("id,loan_id,user_full_name,relationship_borrower,");
		sql.append(
				"(select original_name from o2o_datalist l where  l.loanapply_no = u.zx_no and l.datalist_code='ZXBGDATA' and l.is_delete = 'N' limit 1) as original_name,");
		sql.append(
				"(select original_name from o2o_datalist l where  l.loanapply_no = u.zx_no and l.datalist_code='Warrant_ZXBGDATA' and l.is_delete = 'N' limit 1) as original_name1,");
		sql.append("relation_date,relation_name ");
		sql.append(" from o2o_loan_user u  where u.sponsor_type = 'D' ");
		if (StringUtil.isNotBlank(loanUserForm.getLoanapplyNo())) {
			sql.append(" and u.loanapply_no = ? ");
			pageQuery.addParam(loanUserForm.getLoanapplyNo());
		}
		pageQuery.setQuerySql(sql.toString());
		PageResult<Object[]> og = loanDao.findDataWithPage(pageQuery);
		return og;
	}

	public PageResult<Object[]> findSponsorPersonal(LoanUserForm loanUserForm) {
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<Object[]>(Object[].class, Integer.MAX_VALUE, 0);
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT t.* from (  ");
		sql.append(" SELECT  ");
		sql.append(
				" (select datalist_no from o2o_datalist l where  l.loanapply_no = u.zx_no and l.datalist_code='ZXBGDATA' and l.is_delete = 'N' ) as datalist_no, ");
		sql.append(
				" (select datalist_no from o2o_datalist l where  l.loanapply_no = u.zx_no and l.datalist_code='Warrant_ZXBGDATA' and l.is_delete = 'N' ) as datalist_no1, ");
		sql.append(" u.id,u.loan_id,u.user_full_name,u.relationship_borrower,");
		sql.append(
				" (select original_name from o2o_datalist l where  l.loanapply_no = u.zx_no and l.datalist_code='ZXBGDATA' and l.is_delete = 'N' ) as original_name,");
		sql.append(" x.zx_date,");
		sql.append(
				" (select original_name from o2o_datalist l where  l.loanapply_no = u.zx_no and l.datalist_code='Warrant_ZXBGDATA' and l.is_delete = 'N' ) as original_name1,");
		sql.append(" x.zx_td_date,relation_date,relation_name ");
		sql.append(
				" from o2o_loan_user u LEFT JOIN o2o_loan_user_zx x ON u.loanapply_no = x.zx_no where u.sponsor_type = 'D' ");
		if (StringUtil.isNotBlank(loanUserForm.getLoanapplyNo())) {
			sql.append(" and u.loanapply_no = ? ");
			pageQuery.addParam(loanUserForm.getLoanapplyNo());
		} else {
			sql.append(" '' ");
		}
		sql.append(") t where 1=1  ");
		if (StringUtil.isNotBlank(loanUserForm.getLoanapplyNo())) {
			sql.append(" and loan_id = ? ");
			pageQuery.addParam(loanUserForm.getUserId());
		} else {
			sql.append(" '' ");
		}
		pageQuery.setQuerySql(sql.toString());
		PageResult<Object[]> og = loanDao.findDataWithPage(pageQuery);
		return og;
	}

	public PageResult<Object[]> findSponsorLoanapply(LoanUserForm loanUserForm) {
		String[] aStrings = loanUserForm.getUserId().split(",");
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<Object[]>(Object[].class, Integer.MAX_VALUE, 0);
		StringBuffer sql = new StringBuffer();
		if (aStrings.length > 0) {
			sql.append("select ol.loanapply_no,ol.bill_code,");
			sql.append("( SELECT r.user_full_name from o2o_loan_user r where r.loan_id=" + aStrings[0]
					+ " and  r.loanapply_no=ol.loanapply_no and r.sponsor_type = 'D' ) as loannmaee,");
			sql.append(
					"u.user_full_name,ol.ltd_Name,ol.bank_loan_name,ol.loana_mount,t.loan_amount,ol.wf_type,t.loan_date,ol.end_Bank_Date ");
			sql.append(" from o2o_loanapply ol  LEFT JOIN o2o_bank_loan_data t on t.bill_code = ol.bill_code  ");// o2o_bank_loan_data--ba.loan_date放款日期
			sql.append(
					" LEFT JOIN o2o_loan_user u ON u.loanapply_no=ol.loanapply_no and  u.spouse_id is null and u.sponsor_type is null  ");
			sql.append(
					"where ol.loanapply_no in (select loanapply_no from o2o_loan_user where sponsor_type = 'D' and loan_id ="
							+ aStrings[0] + ") ");

		}
		for (int i = 1; i < aStrings.length; i++) {
			sql.append(" UNION ");
			sql.append(" select ol.loanapply_no,ol.bill_code,");
			sql.append("( SELECT r.user_full_name from o2o_loan_user r where r.loan_id=" + aStrings[i]
					+ " and  r.loanapply_no=ol.loanapply_no and r.sponsor_type = 'D' ) as loannmaee,");
			sql.append(
					"u.user_full_name,ol.ltd_Name,ol.bank_loan_name,ol.loana_mount,t.loan_amount,ol.wf_type,t.loan_date,ol.end_Bank_Date ");
			sql.append(" from o2o_loanapply ol  LEFT JOIN o2o_bank_loan_data t on t.bill_code = ol.bill_code  ");// o2o_bank_loan_data--ba.loan_date放款日期
			sql.append(
					" LEFT JOIN o2o_loan_user u ON u.loanapply_no=ol.loanapply_no and  u.spouse_id is null and u.sponsor_type is null  ");
			sql.append(
					"where ol.loanapply_no in (select loanapply_no from o2o_loan_user where sponsor_type = 'D' and loan_id ="
							+ aStrings[i] + ") ");
		}
		// sql.append("select
		// ol.loanapply_no,ol.bill_code,u.user_full_name,ol.ltd_Name,ol.bank_loan_name,ol.loana_mount,t.loan_amount,ol.payment_Time,ol.end_Bank_Date
		// from o2o_loanapply ol ");
		// sql.append(" LEFT JOIN o2o_bank_loan_data t on t.bill_code =
		// ol.bill_code ");
		// sql.append(" LEFT JOIN o2o_loan_user u ON
		// u.loanapply_no=ol.loanapply_no and u.spouse_id is null and
		// u.sponsor_type is null ");
		// sql.append(" where ol.loanapply_no in (select loanapply_no from
		// o2o_loan_user where sponsor_type = 'D' and loan_id in (" );
		// if(StringUtil.isNotBlank(loanUserForm.getUserId())){
		// sql.append(""+loanUserForm.getUserId()+"");
		// }
		// sql.append(")) ");
		pageQuery.setQuerySql(sql.toString());
		PageResult<Object[]> og = loanDao.findDataWithPage(pageQuery);
		return og;
	}

	/**
	 * 担保人check(根据身份证号去查询是否有代偿，或者是未还款完成的单)
	 * 
	 * @param 身份证号
	 * @return
	 */
	public int getCheckCount(String idCard) {
		String sql_j = "select count(*) from o2o_loanapply oly where loanapply_no in ("
				+ "select loanapply_no from o2o_loan_user olu where olu.document_no =? and sponsor_type is null  union  select '' "
				+ ") and oly.wf_type not IN (?,?,?)";
		int j = loanDao.findInteger(sql_j, idCard, StatusContexts.O2O_SB_FAIL, StatusContexts.O2O_REPAY_END,
				StatusContexts.O2O_FK_OVER);
		return j;
	}

	/**
	 * 该客户夫妻双方有未结清的担保单，不能申请贷款
	 * 
	 * @param 身份证号
	 * @return
	 */
	public int getCheckCountD(String idCard) {
		String sql_j = "select count(*) from o2o_loanapply oly where loanapply_no in ("
				+ "select loanapply_no from o2o_loan_user olu where olu.document_no =? and sponsor_type = 'D'  union  select '' "
				+ ") and oly.wf_type not IN (?,?,?)";
		int j = loanDao.findInteger(sql_j, idCard, StatusContexts.O2O_SB_FAIL, StatusContexts.O2O_REPAY_END,
				StatusContexts.O2O_FK_OVER);
		return j;
	}

	/**
	 * @Description:
	 *               <p>
	 *               获取用户贷款次数
	 *               </p>
	 * @author CXW
	 * @date 2017年3月2日 下午1:13:18
	 * @version V1.0
	 */
	public int getLoanerCount(String looanId) {
		String sql = "select count(*) from o2o_loanapply ol where ol.loan_id=? and ol.loan_flg='Y' and wf_type<>'T'";
		return loanDao.findInteger(sql, looanId);
	}

	/**
	 * 根椐贷款单号查询借款人和配偶
	 * 
	 * @param loanapplyNo
	 * @return
	 */

	public List<O2oLoanUserEntity> findloanORspouse(String loanapplyNo) {
		return loanDao.findloanORspouse(loanapplyNo);
	}

	/**
	 * 主表loanapplyNo 查询 主贷款担保人O2oLoanUserEntity实体
	 * 
	 * @param loanapplyNo
	 * @return
	 */
	public O2oLoanUserEntity queryDBById(String loanapplyNo) {
		return loanDao.queryDBById(loanapplyNo);
	}

	/**
	 * @Description:
	 *               <p>
	 *               根据用户id（CRM系统的id）查询第一次贷款的时间
	 *               </p>
	 * @author CXW
	 * @date 2017年8月8日 上午9:39:54
	 * @version V1.0
	 */
	public String getStartLoanTime(String loanId) {
		String sql = "select min(l.payment_time) from o2o_loanapply l where loan_id = ? ";
		return loanDao.findString(sql, loanId);
	}

	public List<O2oLoanUserEntity> queryDBsById(String loanapplyNo) {
		return loanDao.queryDBsById(loanapplyNo);
	}

	/**
	 * @Title: findMateUserEntity
	 * @Description: 查询借款人配偶id
	 * @author 张庆奇
	 * @param loanapplyNo
	 * @return
	 * @return String
	 * @throws @date
	 *             2017年10月12日 下午5:33:46
	 */
	public String findMateUserEntity(String loanapplyNo) {

		return loanDao.findMateUserEntity(loanapplyNo);
	}

	public O2oLoanUserEntity findBySomeone(String loanapplyNo, String idNum) {
		String sql = "select  * from o2o_loan_user  u where u.loanapply_no=? and u.document_no=?  and u.sponsor_type is null";
		return loanDao.findEntity(sql, O2oLoanUserEntity.class, loanapplyNo, idNum);
	}

	public PageResult<Object[]> findByUserName(String username) {
		return loanDao.findByUserName(username);
	}

	/**
	 * 内审提交check 校验借款人和配偶、担保人联系电话是否相同
	 * @param loanapply_no 贷款单id
	 * @return
	 */
	public Boolean getCheckUserPhone(String loanapply_no){
		Boolean flag = false;
		//电话分组
		String sql_j = "select count (*) from (select user_phone from o2o_loan_user where loanapply_no=?  GROUP BY user_phone,loanapply_no ) t ";
		
		//人分组
		String sql_i = "select count (*) from (select user_phone from o2o_loan_user where loanapply_no=?  GROUP BY loan_id,loanapply_no ) t ";
		
		int j = loanDao.findInteger(sql_j,loanapply_no);
		int i = loanDao.findInteger(sql_i,loanapply_no);
		if(i>j){
			flag = true;
		}
		return flag;
	}

	/**
	 * 担保人保存check 校验担保人联系电话是否相同
	 * @param userPhone  电话号码
	 * @param loanapply_no 贷款单id
	 * @return
	 */
	public Boolean getDBRCheckUserPhone(String loanapply_no,String userPhone){
		Boolean flag = false;
		String sql_j = "select count(*) from o2o_loan_user where loanapply_no=?  and user_Phone=? ";
		int j = loanDao.findInteger(sql_j,loanapply_no,userPhone);
		if(j>0){
			flag = true;
		}
		return flag;
	}

}
